package fr.km.server;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import fr.km.client.KMService;
import fr.km.client.model.Article;
import fr.km.client.model.tools.ArticleTools;

/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class KMServiceImpl extends RemoteServiceServlet implements
		KMService {

//		String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String DRIVER = "org.apache.derby.jdbc.ClientDriver";
	private static final String DATABASE="//localhost:1527/EasyKM";
//		String dbName="/EasyKM";
	private static final String CONNECTION_URL = "jdbc:derby:" + DATABASE; 
	
	public ArrayList<Article> findArticles(String keywords) {
		final ArrayList<String> tags = new ArrayList<String>();
		 tags.add("J2EE");
		 tags.add("eclipse");
		final Article a = new Article(0, "Title 1", tags, "some content");
		final Article b = new Article(0, "Title 2", tags, "some content");
		final Article c = new Article(0, "Title 3", tags, "some content");
		final Article d = new Article(0, "Title 4", tags, "some content");
		
		Article e = null;

		ArrayList<Article> result = new ArrayList<Article>();
		result.add(a);result.add(b);result.add(c);result.add(d);

		Connection conn = getConnection();
		
		if(conn != null) {
			try {
				Statement st=conn.createStatement();
				ResultSet rs=st.executeQuery("SELECT * FROM ARTICLES");
				
				ArrayList<String> l = new ArrayList<String>();
				while (rs.next()){
					String title=rs.getString("TITLE");
					String tg=rs.getString("TAGS");
					Clob clob = rs.getClob("KNOWLEDGE");
					String[] split = tg.split(",");
					for(String s : split) {
						l.add(s.trim());
					}
					e = new Article(0, title, l, clob.getSubString(1l, (int)clob.length()));
					result.add(e);
				}
				rs.close();
			} catch(Exception ex) {
				ex.printStackTrace();
			} finally {
				closeConnection(conn);
			}
		}
		
		return result;
	}

	@Override
	public Article updateArticle(Article article) {
		if(article.getId() == Article.UNDEFINED) {
			return insert(article);
		} else {
			update(article);
			return article;
		}
	}
	
	private Article insert(Article article) {
		String sql = "INSERT INTO ARTICLES (TITLE, KNOWLEDGE, TAGS) VALUES (?, ?, ?)";
		Connection connection = getConnection();
		if(connection != null ) {
			try {
				PreparedStatement st = connection.prepareStatement(sql);
				st.setString(1, article.getTitle());
				st.setString(2, article.getContent());
				st.setString(3, ArticleTools.getTagsAsString(article));
				st.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return article;
	}
	
	private Article update(Article article) {
		return article;
	}
	
	private Connection getConnection() {
		Connection conn = null;
		try{
			Class.forName(DRIVER).newInstance();
			conn = DriverManager.getConnection(CONNECTION_URL); 
		} catch(Exception ex) {
		    ex.printStackTrace();
		}
		return conn;
	}
	
	private void closeConnection(Connection conn) {
		try {
			conn.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
	}
}
